package com.houseman.workFlow.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.houseman.workFlow.bean.Role;
import com.houseman.workFlow.dao.RoleDao;
import com.houseman.workFlow.dto.Approver;
import com.houseman.workFlow.utils.DBUtils;

public class RoleDaoImpl implements RoleDao{

	public List<Approver> queryApproverById(String roleId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT U.USERID,U.USERNAME FROM ROLE R,USER U,USERROLE L WHERE U.USERID=L.USERID AND L.ROLEID=R.ROLEID AND R.ROLEID=?");
		ps.setString(1, roleId);
		
		ResultSet rs = ps.executeQuery();
		List<Approver> apps = new ArrayList<Approver>();
		while (rs.next()) {
			Approver app = new Approver();
			app.setUserId(rs.getString("USERID"));
			app.setUserName(rs.getString("USERNAME"));
			
			apps.add(app);
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return apps;
	}

	public List<Role> queryRoles() throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT ROLEID,ROLENAME FROM ROLE");
		
		ResultSet rs = ps.executeQuery();
		List<Role> roles = new ArrayList<Role>();
		while (rs.next()) {
			Role r = new Role();
			r.setRoleId(rs.getString("ROLEID"));
			r.setRoleName(rs.getString("ROLENAME"));
			
			roles.add(r);
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return roles;
	}

}
